package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import org.json.JSONArray;
import org.json.JSONObject;

import fy.config.FyConfig;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class FundsOpeatorDao {
	private static final String QUERY_COST_SUBJECT = "select a.costname, a.pk_costsubj from v_fyfund_costsubj_for_oa a";
	private static final String QUERY_CORP = "select c.pk_corp, c.unitname from bd_corp c left join bd_corp c1 on c.fathercorp = c1.pk_corp where c.pk_corp not in ('1001','1003','1016') order by c.pk_corp";

	private static int queryOptionCount = 0;
	private static String optionResult = null;

	private static final String QUERY_FUND_CORP_PLAN = "select c.unitname, f.pk_corp, f.pk_cash_plan, f.currtypecode, f.plan_amount, f.bill_no, f.opp_corp, f.staff_id, f.plan_date, f.costname, f.pk_costsubj from v_fyfund_corpplan_for_oa f left join bd_corp c on f.pk_corp = c.pk_corp where f.staff_id = ? and f.bill_no like '%'||?||'%' and to_date(f.plan_date, 'yyyy-mm-dd') > to_date(?, 'yyyy-mm-dd') and to_date(f.plan_date, 'yyyy-mm-dd') < to_date(?, 'yyyy-mm-dd')";

	public static String queryOption(String baoxiao) {
		if (queryOptionCount > 10) {
			optionResult = null;
			queryOptionCount = 0;
		}
		if (optionResult == null) {
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet rs = null;
			JSONObject result = new JSONObject();
			boolean success = false;
			try {
				conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());


				if ("true".equals(baoxiao)) {
					pst = conn.prepareStatement(QUERY_COST_SUBJECT);

					rs = pst.executeQuery();

					while (rs.next()) {
						JSONObject jo = new JSONObject();
						jo.put("name", rs.getString(1));
						jo.put("key", rs.getString(2));
						result.append("cost", jo);
						jo = null;
					}
					DBUtil.close(rs, pst);
				}

				pst = conn.prepareStatement(QUERY_CORP);
				rs = pst.executeQuery();
				while (rs.next()) {
					JSONObject jo = new JSONObject();
					jo.put("name", rs.getString(2));
					jo.put("key", rs.getString(1));
					result.append("corp", jo);
					jo = null;
				}
				success = true;
				result.put("success", success);
			} catch (Exception e) {
				DBUtil.rollback(conn);
				e.printStackTrace();
			} finally {
				DBUtil.close(rs, pst, conn);
			}
			if (success) {
				optionResult = result.toString();
				result = null;
			} else {
				return "{success:false, data : '查询制造部和报销类型异常，请重试。'}";
			}
		} else {
			queryOptionCount++;
		}
		return optionResult;
	}

	public static String queryFundCorpPlan(String member, String bill_no,
			String begain, String end) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		JSONObject result = null;
		String msg = null;
		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

			pst = conn.prepareStatement(QUERY_FUND_CORP_PLAN);

			pst.setString(1, member);

			pst.setString(2, bill_no);
			pst.setString(3, MyUtil.strIsNotNull(begain) ? begain
					: "2000-01-01");
			pst.setString(4, MyUtil.strIsNotNull(end) ? end : "9000-01-01");

			rs = pst.executeQuery();
			JSONArray ja = new JSONArray();
			while (rs.next()) {
				JSONObject jo = new JSONObject();
				ResultSetMetaData md = rs.getMetaData();
				for (int i = 0, size = md.getColumnCount(); i < size; i++) {
					String name = md.getColumnName(i+1);
					jo.put(name.toLowerCase(), rs.getString(name));
					name = null;
				}
				ja.put(jo);
				jo = null;
				md = null;
			}
			result = new JSONObject();
			result.put("data", ja);
			result.put("success", true);
			ja = null;
		} catch (Exception e) {
			DBUtil.rollback(conn);
			e.printStackTrace();
			msg = "{success: false, data:'访问数据库发生异常，请重试，或者联系OA系统管理员。'}";
		} finally {
			DBUtil.close(rs, pst, conn);
		}
		return result == null ? msg : result.toString();
	}
}
